﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySkiPatrolEntity;
using System.Data;
using MySkiPatrolSQL;

namespace MySkiPatrolDAL
{
    public class PatrolDAO
    {
        SqlConnector SQLCONN = new SqlConnector();

        public List<AppSetting> GetAppSettings(Patrol data)
        {
            List<AppSetting> myReturn = new List<AppSetting>();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.GetAppSettings(data), "data");
                DataTable dt = ds.Tables["data"];

                foreach (DataRow dr in dt.Rows)
                {
                    AppSetting item = new AppSetting();

                    try
                    {
                        item.ID = DataConvertor.toString(dr, "SettingID");
                        item.Name = DataConvertor.toString(dr, "SettingName");
                        item.Value = DataConvertor.toString(dr, "SettingValue");
                    }
                    catch (Exception ex)
                    {
                        if (item.DataTransaction == null) item.DataTransaction = new DataTransaction();
                        if (item.DataTransaction.TransactionErrors == null) item.DataTransaction.TransactionErrors = new List<Error>();
                        //item.DataTransaction.TransactionErrors.Add(new Error(0000, ex.Message));
                    }

                    myReturn.Add(item);
                }


            }
            catch (System.Exception ex)
            {
                throw ex;
            }

            return myReturn;
        }

        public Patrols GetPatrolList()
        {
            //setup the return object
            Patrols myReturn = new Patrols();
            myReturn.PatrolList = new List<Patrol>();
            if (myReturn.DataTransaction == null) myReturn.DataTransaction = new DataTransaction();
            if (myReturn.DataTransaction.TransactionErrors == null) myReturn.DataTransaction.TransactionErrors = new List<Error>();
            if (myReturn.DataTransaction.TransactionLog == null) myReturn.DataTransaction.TransactionLog = new List<LogEntry>();
            myReturn.DataTransaction.Success = true;
            myReturn.DataTransaction.Action = DataAction.View;

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.GetPatrolList(), "data");
                DataTable dt = ds.Tables["data"];

                foreach (DataRow dr in dt.Rows)
                {
                    Patrol item = new Patrol();

                    item.Name = DataConvertor.toString(dr, "PatrolName");
                    item.ID = DataConvertor.toString(dr, "PatrolID");
                    item.Title = DataConvertor.toString(dr, "PatrolTitle");
                    item.dbPrefix = DataConvertor.toString(dr, "dbPrefix");
                    item.SubDomain = DataConvertor.toString(dr, "SubDomain");

                    //switch (DataConvertor.toString(dt.Rows[0], "SiteStatus"))
                    //{
                    //    case "A":
                    //        data.Status = SiteStatus.Active;
                    //        break;
                    //    case "X":
                    //        data.Status = SiteStatus.Expired;
                    //        break;
                    //    default:
                    //        data.Status = SiteStatus.Trial;
                    //        break;
                    //}

                    myReturn.PatrolList.Add(item);
                }
            }
            catch (System.Exception ex)
            {
                myReturn.DataTransaction.Success = false;
                myReturn.DataTransaction.TransactionErrors.Add(new Error("DAO10001", "An error occurred collecting the list of patrols"));
            }

            return myReturn;

        }

        public Patrol GetPatrolBySubDomain(Patrol data)
        {
            //setup the return object
            if (data.DataTransaction == null) data.DataTransaction = new DataTransaction();
            if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
            if (data.DataTransaction.TransactionLog == null) data.DataTransaction.TransactionLog = new List<LogEntry>();
            data.DataTransaction.Success = true;
            data.DataTransaction.Action = DataAction.View;

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.GetPatrolBySubDomain(data), "data");
                DataTable dt = ds.Tables["data"];

                foreach (DataRow dr in dt.Rows)
                {
                    data.Name = DataConvertor.toString(dt.Rows[0], "PatrolName");
                    data.ID = DataConvertor.toString(dt.Rows[0], "PatrolID");
                    data.Title = DataConvertor.toString(dt.Rows[0], "PatrolTitle");
                    data.dbPrefix = DataConvertor.toString(dt.Rows[0], "dbPrefix");
                    data.SubDomain = DataConvertor.toString(dt.Rows[0], "SubDomain");

                    //switch (DataConvertor.toString(dt.Rows[0], "SiteStatus"))
                    //{
                    //    case "A":
                    //        data.Status = SiteStatus.Active;
                    //        break;
                    //    case "X":
                    //        data.Status = SiteStatus.Expired;
                    //        break;
                    //    default:
                    //        data.Status = SiteStatus.Trial;
                    //        break;
                    //}
                }
            }
            catch (System.Exception ex)
            {
                data.DataTransaction.Success = false;
                data.DataTransaction.TransactionErrors.Add(new Error("DAO10002", "An error occurred collecting the patrol information using the SubDmain"));
            }

            return data;
        }

        public Patrol GetPatrolByID(Patrol data)
        {
            //setup the return object
            if (data.DataTransaction == null) data.DataTransaction = new DataTransaction();
            if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
            if (data.DataTransaction.TransactionLog == null) data.DataTransaction.TransactionLog = new List<LogEntry>();
            data.DataTransaction.Success = true;
            data.DataTransaction.Action = DataAction.View;

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.GetPatrolByID(data), "data");
                DataTable dt = ds.Tables["data"];

                foreach (DataRow dr in dt.Rows)
                {
                    data.Name = DataConvertor.toString(dt.Rows[0], "PatrolName");
                    data.ID = DataConvertor.toString(dt.Rows[0], "PatrolID");
                    data.Title = DataConvertor.toString(dt.Rows[0], "PatrolTitle");
                    data.dbPrefix = DataConvertor.toString(dt.Rows[0], "dbPrefix");
                    data.SubDomain = DataConvertor.toString(dt.Rows[0], "SubDomain");

                    //switch (DataConvertor.toString(dt.Rows[0], "SiteStatus"))
                    //{
                    //    case "A":
                    //        data.Status = SiteStatus.Active;
                    //        break;
                    //    case "X":
                    //        data.Status = SiteStatus.Expired;
                    //        break;
                    //    default:
                    //        data.Status = SiteStatus.Trial;
                    //        break;
                    //}
                }
            }
            catch (System.Exception ex)
            {
                data.DataTransaction.Success = false;
                data.DataTransaction.TransactionErrors.Add(new Error("DAO10003", "An error occurred collecting the patrol information using the ID"));
            }

            return data;
        }

        public Patrol AddPatrol(Patrol data)
        {
            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.AddPatrol(data), "temp");
                DataTable dt = ds.Tables["temp"];
                data.DataTransaction.Success = true;
            }
            catch (Exception ex)
            {
                if (data.DataTransaction == null) data.DataTransaction = new DataTransaction();
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO10004", "An error occurred adding the patrol record/r" + ex.Message));
                data.DataTransaction.Success = false;
            }

            return data;
        
        }

        public Patrol CreateMemberTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the member table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateMemberTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateMemberTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully create the member table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the member table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateEmailTemplateTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the emailtemplate table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateEmailTemplateTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateEmailTemplateTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the emailtemplate table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the emailtemplate table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateEmailLogTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the EmailLog table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateEmailLogTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateEmailLogTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the EmailLog table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the EmailLog table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateEmailAddressTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the EmailAddress table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateEmailAddressTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateEmailAddressTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the EmailAddress table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the EmailAddress table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateApplicationSettingTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the ApplicationSetting table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateApplicationSettingTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateApplicationSettingTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the ApplicationSetting table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the ApplicationSetting table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateArticleTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the Article table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateArticleTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateArticleTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the Article table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the Article table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateGroupRoleTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the GroupMembership table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateGroupRoleTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateGroupRoleTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the GroupMembership table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the GroupMembership table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateRoleMemberTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the RoleMember table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateRoleMemberTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateRoleMemberTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the RoleMember table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the RoleMember table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateGroupTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the Group table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateGroupTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateGroupTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the Group table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the Group table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateRoleTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the Role table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateRoleTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateRoleTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the Role table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the Role table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateEventTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the Event table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateEventTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateEventTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the Event table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the Event table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateServiceDaysTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the ServiceDays table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateServiceDaysTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateServiceDaysTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the ServiceDays table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the ServiceDays table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateDailySignInTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the DailySignIn table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateDailySignInTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateDailySignInTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the DailySignIn table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the DailySignIn table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public Patrol CreateMemberIDCardsTable(Patrol data)
        {
            try
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Creating the MemberIDCards table for " + data.Name + " (" + data.dbPrefix + ")"));
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, PatrolSQL.CreateMemberIDCardsTable(data)));
                DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.CreateMemberIDCardsTable(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "Successfully created the MemberIDCards table for " + data.Name + " (" + data.dbPrefix + ")"));
            }
            catch (Exception ex)
            {
                data.DataTransaction.TransactionLog.Add(new LogEntry(DateTime.Now, "An error occurred creating the MemberIDCards table for " + data.Name + " (" + data.dbPrefix + ")/r" + ex.Message));
                //myReturn = false;
            }

            return data;
        }

        public List<WeatherForecast> GetWeatherForecasts(string patrolPrefix)
        {
            List<WeatherForecast> myReturn = new List<WeatherForecast>();

            DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.GetWeatherForecasts(patrolPrefix), "temp");
            DataTable dt = ds.Tables["temp"];

            foreach (DataRow dr in dt.Rows)
            {
                WeatherForecast item = new WeatherForecast();
                item.Data = DataConvertor.toString(dr, "weatherforecastdata");
                item.HTML = DataConvertor.toString(dr, "weatherforecasthtml");
                item.DayCount = DataConvertor.toString(dr, "daycount");
                item.ID = DataConvertor.toString(dr, "weatherwatcherid");
                item.LastUpdate = DataConvertor.toDateTime(dr, "lastupdate");
                item.Location = DataConvertor.toString(dr, "location");
                item.Name = DataConvertor.toString(dr, "weatherwatchername");
                item.RefreshInterval = DataConvertor.toString(dr, "updateintervalminutes");

                myReturn.Add(item);
            }

            return myReturn;
        }

        public void UpdateWeatherData(WeatherForecast data, string patrolPrefix)
        {
            DataSet ds = SQLCONN.RunSQL_DataSet(PatrolSQL.UpdateWeatherForecast(data, patrolPrefix), "temp");
            DataTable dt = ds.Tables["temp"];
        }
    }
}
